/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author 013v
 */
public class ProductManager {

    public ResultSet searchProduct(String newProd) {

        try {

            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT FakeProductID as 'ProductID', ProductTitle, ProductSynopsis, "
                    + "ProductPrice, py.ProductType as 'ProductType' FROM product p inner"
                    + " join productidtrack pt on p.ProductID = pt.ProductID right "
                    + "join producttype py on p.ProductType = py.ProductTypeID "
                    + "where ProductTitle like ? OR ProductSynopsis like ? and ProductStatus = 1";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(1, "%" + newProd + "%");
            pstmt.setString(2, "%" + newProd + "%");

            ResultSet res = pstmt.executeQuery();
            return res;

        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public ResultSet getProductTypes(){
         try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT * FROM producttype";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

           // pstmt.setInt(1, prdtype);

            ResultSet res = pstmt.executeQuery();
            return res;

        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }
    public ResultSet getProductPerType(int prdtype) {

        try {
//            int prdtype = 0;
//            if (newProd.equals("book")) {
//                prdtype = 1;
//            } else if (newProd.equals("mag")) {
//                prdtype = 2;
//            } else if (newProd.equals("cd")) {
//                prdtype = 3;
//            } else if (newProd.equals("dvd")) {
//                prdtype = 4;
//            }

            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT FakeProductID as 'ProductID', ProductTitle, ProductSynopsis, "
                    + "ProductPrice FROM product p inner join productidtrack pt "
                    + "on p.ProductID = pt.ProductID where ProductType = ? "
                    + "and ProductStatus = 1";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setInt(1, prdtype);

            ResultSet res = pstmt.executeQuery();
            return res;

        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }
    
    public int getOProdID(String PID){
        int result = 0;
        try{
         DBConnection db = new DBConnection();
         Connection conn = db.getConnection();
         
         String que = "SELECT ProductID FROM productidtrack where FakeProductID = ?";
         PreparedStatement pstat = conn.prepareStatement(que);
         pstat.setString(1, PID);
         
         ResultSet rst = pstat.executeQuery();
         
         if(rst.next())
         result = rst.getInt("ProductID");
         
         conn.close();
         
        }catch(SQLException ex){
            ex.printStackTrace();
    }
        return result;
    }

    public Product getProductDetails(String PID) {
        Product result = null;
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT FakeProductID as 'ProductID', ProductTitle, ProductSynopsis, "
                    + "ProductPrice, ProductRating FROM product p inner join productidtrack pt "
                    + "on p.ProductID = pt.ProductID where pt.FakeProductID = ? "
                    + "and ProductStatus = 1";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(1, PID);

            ResultSet rst = pstmt.executeQuery();

            //get data
            result = new Product();
            while (rst.next()) {
                result.setProductID(rst.getInt("ProductID"));
                result.setProductTitle(rst.getString("ProductTitle"));
                result.setProductSynopsis(rst.getString("ProductSynopsis"));
                result.setProductPrice(rst.getString("ProductPrice"));
                result.setProductRating(rst.getString("ProductRating"));
            }

            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public boolean addProdIDTrack(Product newProd, int newNum) {
        boolean result = false;

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "INSERT into productidtrack(ProductID, FakeProductID) VALUES(?,?)";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setInt(1, newProd.getProductID());
            pstmt.setInt(2, newNum);

            pstmt.executeUpdate();
            //close DB connection
            conn.close();

            result = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return result;
    }

    public boolean addProduct(Product newProd) {
        boolean result = false;

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            int i = 1;

            //String que = "INSERT into product(ProductID, ProductTitle, ProductSynopsis, ProductRating, ProductPrice, ProductType, ProductStatus) VALUES(?,?,?,?,?,?,?)";
            PreparedStatement pstmt =
                    conn.prepareStatement("INSERT "
                    + "INTO product(ProductID,ProductTitle,ProductSynopsis,ProductRating,ProductPrice,ProductType,ProductQuantity,ProductStatus)"
                    + " values(?,?,?,?,?,?,?,?)");

            pstmt.setInt(i++, newProd.getProductID());
            pstmt.setString(i++, newProd.getProductTitle());
            pstmt.setString(i++, newProd.getProductSynopsis());
            pstmt.setString(i++, newProd.getProductRating());
            pstmt.setString(i++, newProd.getProductPrice());
            pstmt.setInt(i++, newProd.getProductType());
            pstmt.setInt(i++, newProd.getProductQuantity());
            pstmt.setInt(i++, 1);

            pstmt.executeUpdate();
            //close DB connection
            conn.close();

            result = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return result;

    }

    public boolean cancelProduct(Product newProd) {
        boolean result = false;

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "UPDATE product SET ProductStatus = 0 WHERE ProductID = ?";
            PreparedStatement pstat = conn.prepareStatement(que);
            pstat.setInt(1, newProd.getProductID());

            pstat.executeUpdate();

            result = true;
            conn.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public boolean editProduct(Product newProd) {
        boolean result = false;

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "UPDATE product SET ProductTitle = ?, ProductSynopsis = ?, ProductRating = ?, ProductPrice = ?, ProductQuantity = ? WHERE ProductID = ?";
            PreparedStatement pstat = conn.prepareStatement(que);
            int i = 1;
            pstat.setString(i++, newProd.getProductTitle());
            pstat.setString(i++, newProd.getProductSynopsis());
            pstat.setString(i++, newProd.getProductRating());
            pstat.setString(i++, newProd.getProductPrice());
            pstat.setInt(i++, newProd.getProductQuantity());
            pstat.setInt(i++, newProd.getProductID());

            pstat.executeUpdate();

            result = true;
            conn.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }
    
    public String getProdName(String prodId){
         String result = null;
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT ProductTitle FROM product p inner join productidtrack pt "
                    + "on p.ProductID = pt.ProductID where pt.FakeProductID = ? "
                    + "and ProductStatus = 1";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(1, prodId);

            ResultSet rst = pstmt.executeQuery();

            //get data
            if(rst.next())
                result = rst.getString("ProductTitle");
                
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }
    
    
    public String getProdPrice(String prodId){
         String result = null;
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT ProductPrice FROM product p inner join productidtrack pt "
                    + "on p.ProductID = pt.ProductID where pt.FakeProductID = ? "
                    + "and ProductStatus = 1";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(1, prodId);

            ResultSet rst = pstmt.executeQuery();

            //get data
            if(rst.next())
                result = rst.getString("ProductPrice");
                
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }
}
